package com.szholly.pro.watergas_server.ps.android.service;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.util.CollectionUtils;

import com.szholly.pro.watergas_server.common.data.DataUtil;
import com.szholly.pro.watergas_server.ps.android.common.FieldInterceptor;
import com.szholly.pro.watergas_server.ps.android.common.NonNullUtil;
import com.szholly.pro.watergas_server.ps.android.common.SqlUtils;
import com.szholly.pro.watergas_server.ps.android.common.ValidUtils;
import com.szholly.pro.watergas_server.ps.android.controller.QcControl;
import com.szholly.pro.watergas_server.ps.data.QcBatchRow;
import com.szholly.pro.watergas_server.ps.data.QcBatchTable;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.RowBase;

/**
 * 批次相关的数据
 */
public class QcBatchService {
	public static QcBatchService INSTANCE = new QcBatchService();
	protected QcParameterService mParameterService = QcParameterService.INSTANCE;

	private QcBatchService() {
	}

	/**
	 * 获取采样的批次信息
	 */
	public List<RowBase> getSampleBatchList(String companyCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("companyCode", companyCode));
		String sql = "select t.id \"id\",\n"
				+ "       t.des \"des\",\n"
				+ "       case status\n"
				+ "         when '0' then\n"
				+ "          '待采样'\n"
				+ "         when '1' then\n"
				+ "          '待分析'\n"
				+ "         when '2' then\n"
				+ "          '已分析'\n"
				+ "       end \"name\",\n"
				+ "       to_char(t.START_TIME, 'yyyy-mm-dd hh24:mi:ss') \"startTime\",\n"
				+ "       to_char(t.END_TIME, 'yyyy-mm-dd hh24:mi:ss') \"endTime\",\n"
				+ "       t.status \"type\"\n"
				+ "  from qc_batch t\n"
				+ " where 1 = 1\n"
				+ "   and t.company = @companyCode\n"
				+ "   and t.status in ('0', '1')\n"
				+ " order by t.start_time desc";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		return NonNullUtil.toList(rows);
	}

	/**
	 * 获取实验批次信息
	 */
	public List<RowBase> getLabBatchList(String companyCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("companyCode", companyCode));
		String sql = "select t.id \"id\",\n"
				+ "       t.des \"des\",\n"
				+ "       case status\n"
				+ "         when '0' then\n"
				+ "          '待采样'\n"
				+ "         when '1' then\n"
				+ "          '待分析'\n"
				+ "         when '2' then\n"
				+ "          '已分析'\n"
				+ "       end \"name\",\n"
				+ "       to_char(t.START_TIME, 'yyyy-mm-dd hh24:mi:ss') \"startTime\",\n"
				+ "       to_char(t.END_TIME, 'yyyy-mm-dd hh24:mi:ss') \"endTime\",\n"
				+ "       t.status \"type\"\n"
				+ "  from qc_batch t\n"
				+ " where 1 = 1\n"
				+ "   and t.company = @companyCode\n"
				+ "   and t.status in ('1', '2')\n"
				+ " order by t.start_time desc";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		return NonNullUtil.toList(rows);
	}

	/**
	 * 增加实验批次信息
	 */
	public RowBase addBatch(String batchCode, String companyCode, String des, String userCode) throws Exception {
		makeSureBatchUnique(batchCode);
		QcBatchRow row = new QcBatchRow();
		row.setDes(des);
		row.setCreateUser(userCode);
		row.setCompany(companyCode);
		row.setId(batchCode);
		row.setStatus("0");
		row.setStartTime(new Date());
		FieldInterceptor interceptor = FieldInterceptor.createNotNullInterceptor(QcBatchRow.FIELD_Id, QcBatchRow.FIELD_Company,
				QcBatchRow.FIELD_Status, QcBatchRow.FIELD_CreateUser);
		SqlUtils.insert(QcBatchTable.TABLE_NAME, row, interceptor);
		List<RowBase> batchList = getSampleBatchList(companyCode);
		for (RowBase rowBase : batchList) {
			String id = (String) rowBase.get("id");
			if (batchCode.equals(id)) {
				return rowBase;
			}
		}
		throw new Exception("添加异常");
	}

	/**
	 * 获取待处理批次数
	 */
	public Map<String, String> getTaskCountGroupByCompany(String taskTypeCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		if (QcControl.LAB.equals(taskTypeCode)) {
			parms.add(new GlobalParameter("status", "1"));
		} else {
			parms.add(new GlobalParameter("status", "0"));
		}
		String sql = "select count(t.company) count, t.company\n" + "  from qc_batch t\n" + " where t.status = @status\n" + " group by t.company";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);

		Map<String, String> resultMap = new HashMap<>();
		for (RowBase rowBase : rows) {
			String count = rowBase.getSafeString("count");
			String company = rowBase.getSafeString("company");
			resultMap.put(company, count);
		}
		return resultMap;
	}

	/**
	 * 获取待处理批次数
	 */
	public Map<String, String> getTaskCountGroupByTaskType(String companyCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		String sql = "select count(t.status) count, t.status\n" + "  from qc_batch t\n" + " where t.status in ('0', '1')\n";
		if (!StringUtils.IsNullOrSpace(companyCode)) {
			parms.add(new GlobalParameter("companyCode", companyCode));
			sql += "   and t.company = @companyCode\n";
		}
		sql += " group by t.status";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		Map<String, String> resultMap = new HashMap<>();
		for (RowBase rowBase : rows) {
			String count = rowBase.getSafeString("count");
			String status = rowBase.getSafeString("status");
			if ("0".equals(status)) {
				resultMap.put(QcControl.SAMPLE.getId(), count);
			} else if ("1".equals(status)) {
				resultMap.put(QcControl.LAB.getId(), count);
			}
		}
		return resultMap;
	}

	/**
	 * 删除批号
	 */
	public void delete(String batchCode) throws Exception {
		ValidUtils.hasText(batchCode, "批号为空");
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("batchCode", batchCode));
		String sql = "delete from qc_batch t where t.id = @batchCode";
		DataUtil.ExecuteUpdateSql(sql, parms);
	}

	/**
	 * 获取批次信息 -- 批次包含的样品编号<>
	 */
	public Map<String, Map<String,List<String>>>  getSampleCodeMap(String batchCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("batchCode", batchCode));
		String sql = "select distinct t2.monitortype,t2.samplenumber,t3.monitorparam\n"
				+ "  from qc_batch t\n"
				+ "  left join operation_task t1\n"
				+ "    on t.id = t1.parent_task_code\n"
				+ "  left join qc_sampledata t2\n"
				+ "    on t2.operation_task_code = t1.task_code\n"
				+ "  left join qc_sampledetaildata t3\n"
				+ "    on t3.type = '1'\n"
				+ "   and t3.samplenumber = t2.samplenumber\n"
				+ " where 1 = 1\n"
				+ "   and t2.samplenumber is not null\n"
				+ "   and t.id = @batchCode";

		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		Map<String, Map<String,List<String>>> resultMap = new HashMap<>();

		for (RowBase rowBase : rows) {
			String monitorTypeCode = rowBase.getSafeString("monitortype");
			String sampleCode = rowBase.getSafeString("samplenumber");
			String parameterCode = rowBase.getSafeString("monitorparam");
			if (!resultMap.containsKey(monitorTypeCode)) {
				resultMap.put(monitorTypeCode, new HashMap<>());
			}
			Map<String, List<String>> map = resultMap.get(monitorTypeCode);
			if (!map.containsKey(parameterCode)) {
				map.put(parameterCode, new ArrayList<>());
			}
			map.get(parameterCode).add(sampleCode);
		}
		return resultMap;
	}

	/**
	 * 获取批次信息 -- 监测类型
	 */
	public List<RowBase> getMonitorTypeList(String batchCode) throws Exception {
		ValidUtils.hasText(batchCode);
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("batchCode", batchCode));
		String sql = "select t.s_domaincode as \"id\", t.s_domaincaption as \"name\"\n"
				+ "  from DATA_DEFULATDOMAIN t\n"
				+ " where t.s_domainname = '质控监测类型'\n"
				+ "   and t.s_domaincode in (select distinct t2.monitortype\n"
				+ "                            from qc_batch t\n"
				+ "                            left join operation_task t1\n"
				+ "                              on t.id = t1.parent_task_code\n"
				+ "                            left join qc_sampledata t2\n"
				+ "                              on t2.operation_task_code = t1.task_code\n"
				+ "                           where 1 = 1\n"
				+ "                             and t2.samplenumber is not null\n"
				+ "                             and t.id = @batchCode)\n"
				+ " order by to_number(t.s_domaincode) asc";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		return NonNullUtil.toList(rows);
	}

	/**
	 * 获取批次信息 -- 实验方法
	 */
	public List<RowBase> getLabMethodList(String batchCode) throws Exception {
		ValidUtils.hasText(batchCode);
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("batchCode", batchCode));
		String sql = "select t.METHODCODE       \"methodCode\",\n"
				+ "       t.METHODNAME       \"methodName\",\n"
				+ "       t.MONITORPARAMCODE \"parameterCode\",\n"
				+ "       t.monitortypecode  \"monitorTypeCode\"\n"
				+ "  from qc_expmethod t\n"
				+ "  left join qc_batch t1\n"
				+ "    on t.companycode = t.companycode\n"
				+ " where 1 = 1\n"
				+ "   and t1.id = @batchCode\n"
				+ "   and t.monitorparamcode in\n"
				+ "       (select distinct t3.monitorparam\n"
				+ "          from qc_batch t\n"
				+ "          left join operation_task t1\n"
				+ "            on t.id = t1.parent_task_code\n"
				+ "          left join qc_sampledata t2\n"
				+ "            on t2.operation_task_code = t1.task_code\n"
				+ "          left join qc_sampledetaildata t3\n"
				+ "            on t3.samplenumber = t2.samplenumber\n"
				+ "           and t3.type = '1'\n"
				+ "         where 1 = 1\n"
				+ "           and t2.samplenumber is not null\n"
				+ "           and t.id = @batchCode)";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		return NonNullUtil.toList(rows);
	}

	// 确保批号唯一
	private void makeSureBatchUnique(String batchCode) throws Exception {
		List<GlobalParameter> parms = new ArrayList<GlobalParameter>();
		parms.add(new GlobalParameter("batchCode", batchCode));
		String sql = "select * from qc_batch t where t.id = @batchCode";
		List<RowBase> rows = DataUtil.getSelectRows(sql, parms);
		ValidUtils.isTrue(CollectionUtils.isEmpty(rows), "批号已存在");
	}
}
